Enersight uses OData to allow users to export outcomes to applications such as Spotfire, Tableau and Excel to read results data from plan data sets. This export allows users to use Enersight calculation results externally and create custom visualizations or reports. While it is possible to connect these applications directly to the OData URL, due to the size of the data typically involved, we recommend using SSIS and SQL to filter down the results set to only those pertinent to your specific use case.
For reference, OData is a standard protocol for the transmittal of data over the Internet. By default Enersight uses the OData protocol. From the PDS Manager, OData connections can be generated for easy data export into Excel, Spotfire, Tableau and many other applications.
Managing an OData Link
OData links may be created, cleared, refreshed, redirected and deleted from the Manage Plan Data set dialog (Enterprise > Manage Plan Data Sets).
To download plan data set results through OData, users must access the Manage Plan Data Sets dialog (Enterprise > Manage Plan Data Sets).
Click image to expand or minimize.
After navigating to the Plan Data Set for which you want to adjust the OData settings, you may see and generate the specific links on the right hand side of the dialog (1), as well as adjust whether the OData Link is refreshed automatically upon updated save of a PDS Version (2). The specifics of this auto-refresh include removing any stored data from the cache if it is non-actively being downloaded at the time.
Click image to expand or minimize.
Through this dialog, the user clicks Create Link to generate a unique link that can be copied into an application that supports OData.
Click image to expand or minimize.
The user may alternative select to reuse the link from another PDS Version via ‘reuse link’ whereby they can scroll through the list of PDS Versions currently associated with a link and select which one they want to re-purpose.
Click image to expand or minimize.
Given that a specific URL is often associated with an IT managed stored procedure, an extra confirmation dialog box has also been added on link deletion to avoid inadvertently breaking these processes.
Managing OData Company Settings
Within the revised OData3 instance, there is the ability to enable server-side filtering of the data such that only sparse (non-zero) row data is sent. Whilst this may be achieved similarly via OData query protocol calls, this significantly simplifies the process. This setting may be adjusted by contacting Quorumand requesting it being turned on.
Managing User Data in OData Interface
Metadata (i.e., UserData) from the AssetUserData table can be consumed by SSIS for the OData3 protocol from 2.12 onwards. On export of this data, a transformation was added where similar legitimate definitions exist within Enersight (e.g., ‘User Data’ and ‘User_Data’) and they will no longer incur name duplication within the OData structure. This has been handled through the addition of an appended sequential number which is applied in an intelligent manner to ensure that the resolution is persistent. In this case ‘User_Data’ would remain as is while ‘User Data’ would become ‘User_Data1’.
Tips and Tricks
The user who initializes an OData download via either hitting the URL for the first time or clicking Create Cache within Manage Plan Data Sets, imprints a couple of details onto the download, specifically their discount rates and method as well as their unit type and scale selection will be fixed onto the cached data. This selection is retained if multiple users with different settings attempt to hit the same link until the cache is recycled either through long deferral in download or from a clear (when either auto-refresh or ‘clear cache’ is manually selected). We recommend that standardized processes with dedicated accounts are utilized to ensure that the right details are consistently selected.
Connecting via Excel
While this is not a recommended long-term solution, value can be achieved through initially accessing data via Excel to allow for interrogation and identification of ideal details within an IT driven implementation.
To connect via Excel:
- Within the external application (Excel), click Data > From Other Sources > From OData Data Feed..
- Provide Enersight login credentials and the link to the plan data set OData connection.
- Select which data to download. The application will connect to Enersight and download the specified data.
Click image to expand or minimize.
Click image to expand or minimize.
Click image to expand or minimize.